package com.sinosoft.hydra.um.utils;

import com.sinosoft.hydra.um.utils.excel.ExportBuilder;
import com.sinosoft.hydra.um.utils.excel.ExportColumn;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;

public class ExcelUtils {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    private static Integer XLS_TYPE = 0;
    private static Integer XLSX_TYPE = 1;
    private static Integer OTHER_TYPE = 2;

    public static Integer getType(final String path) {
        if (StringUtils.isBlank(path)) {
            return OTHER_TYPE;
        }
        String type = StringUtils.substring(path, path.lastIndexOf('.'), path.length());
        type = StringUtils.lowerCase(type);
        String XLS_TYPE_STRING = ".xls";
        String XLSX_TYPE_STRING = ".xlsx";
        if (XLS_TYPE_STRING.equals(type)) {
            return XLS_TYPE;
        } else if (XLSX_TYPE_STRING.equals(type)) {
            return XLSX_TYPE;
        } else {
            return OTHER_TYPE;
        }
    }

    public static Sheet getSheet(String path) {
        return getSheet(path, null);
    }

    private static Integer getTypeByVersion(String versionStr) {
        if (StringUtils.isBlank(versionStr)) {
            return OTHER_TYPE;
        }
        if (versionStr.equals("2003")) return XLS_TYPE;
        if (versionStr.equals("2007")) return XLSX_TYPE;
        return getType(versionStr);
    }

    public static Workbook getWorkbook(String path) {
        return getWorkbook(path, null);
    }

    private static Workbook getWorkbook(String path, String excleVersion) {
        Integer type = StringUtils.isNotBlank(excleVersion)
                ? getTypeByVersion(excleVersion)
                : getType(path);
        InputStream is = null;
        try {
            is = new FileInputStream(path);
            return getWorkbook(is, type);
        } catch (Exception e) {
            logger.error("读取文件异常", e.getMessage());
        }

        return null;
    }

    public static Workbook getWorkbook(InputStream inputStream, Integer type) {
        if (OTHER_TYPE.equals(type) || inputStream == null) {
            return null;
        }

        try {
            if (XLS_TYPE.equals(type)) {//2003
                POIFSFileSystem fs = new POIFSFileSystem(inputStream);
                return new HSSFWorkbook(fs);
            } else {//2007
                return new XSSFWorkbook(inputStream);
            }
        } catch (IOException e) {
            logger.error("ioException is {}", e.getMessage());
        }
        return null;
    }


    private static Sheet getSheet(String path, String excleVersion) {
        Workbook workbook = getWorkbook(path, excleVersion);
        return workbook == null ? null : workbook.getSheetAt(0);
    }

    private static <T> void setTitle(CellStyle cellStyle, HSSFRow row, int celNum, ExportBuilder builder) {
        //先获取总行数
        int size = builder.getExportColumns().size();
        //生成标题
        for (int i = 0; i < size; i++) {
            ExportColumn column = builder.getExportColumns().get(i);
            celNum += 1;
            Cell cell = row.createCell(celNum);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(column.getTitle());
        }
        if (builder.getChild() != null) {
            ExportBuilder exportBuilder = builder.getChild();
            setTitle(cellStyle, row, celNum, exportBuilder);
        }
    }


    private static Map<String, CellStyle> createDataStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        style.setFont(titleFont);
        styles.put("title", style);

        style = wb.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.RIGHT);
        styles.put("data3", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
//		style.setWrapText(true);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        return styles;
    }

    private static <T> void setCelValue(CellStyle cellStyle, Cell cell, T t, ExportColumn column) {
        cell.setCellStyle(cellStyle);
        Object dest = t;
        if (column.getValue() != null) {
            dest = column.getValue();
        } else {
            if (StringUtils.isBlank(column.getField())) {
                dest = column.getColumnConverter().convert(t);
            } else {
                String[] fields = column.getField().split(",");
                int k = 0, ll = fields.length;
                while (k < ll && dest != null) {
                    try {
                        dest = PropertyUtils.getProperty(dest, fields[k]);
                        k++;
                    } catch (Exception e) {
                        e.printStackTrace();
                        dest = null;
                    }
                }
            }
        }
        if (dest != null) {
            if (column.isDict()) {
                dest = DictUtils.getDictLabel((String) dest, column.getDictType(), "");
            }
            if (dest instanceof Date) {
                String d = DateFormatUtils.format((Date) dest, "yyyy-MM-dd HH:mm");
                cell.setCellValue(d);
            } else if (dest instanceof String) {
                cell.setCellValue((String) dest);
            } else if (dest instanceof BigDecimal) {
                cell.setCellValue(((BigDecimal) dest).doubleValue());
            } else if (dest instanceof Integer) {
                cell.setCellValue((Integer) dest);
            }
        } else {
            cell.setCellValue("");
        }
    }

    private static <C, T> void buildData(CellStyle cellStyle, HSSFSheet hs, int rowNum, int startCell, List<T> datas, ExportBuilder builder) {
        int size = builder.getExportColumns().size();
        HSSFRow row;
        int n = rowNum;
        for (T t : datas) {
            int cellRangeNum = builder.getCellRangeNum(t);
            row = hs.getRow(n);
            row = row == null ? hs.createRow(n) : row;
            for (int i = 0; i < size; i++) {
                int cell = startCell + i + 1;
                if (cellRangeNum - 1 > 0) {
                    hs.addMergedRegion(new CellRangeAddress(n, n + cellRangeNum - 1, cell, cell));
                }
                ExportColumn column = builder.getExportColumns().get(i);
                setCelValue(cellStyle, row.createCell(cell), t, column);
            }
            if (builder.getChild() != null) {
                List<C> children = builder.getChildMargeNum(t);
                buildData(cellStyle, hs, n, startCell + size, children, builder.getChild());
            }
            n += cellRangeNum;
        }
    }


    public static <T> byte[] export(String title, List<T> datas, ExportBuilder builder) {
        //第一次遍历获取总行数
        String _title = StringUtils.isBlank(title) ? "" : title;
        HSSFWorkbook fs = new HSSFWorkbook();
        Map<String, CellStyle> styles = createDataStyles(fs);
        HSSFSheet hs = fs.createSheet();
        HSSFRow row = hs.getRow(0);
        row = row == null ? hs.createRow(0) : row;
        row.createCell(0).setCellValue(_title);
        row.setRowStyle(styles.get("title"));
        //渲染标题
        row = hs.getRow(1);
        row = row == null ? hs.createRow(1) : row;
        setTitle(styles.get("header"), row, -1, builder);
        // 获取对象
        buildData(styles.get("data"), hs, 2, -1, datas, builder);
        int last = row.getLastCellNum();
        if (StringUtils.isBlank(_title)) {
            hs.removeRow(hs.getRow(0));
        } else {
            hs.addMergedRegion(new CellRangeAddress(0, 0, 0, last - 1));
        }
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            fs.write(os);
            //ByteArrayInputStream is = new ByteArrayInputStream(workBook.getBytes());
            return os.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        return null;
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private static String getStringCellValue(Cell cell) {
        if (cell == null) {
            return StringUtils.EMPTY;
        }
        String strCell = "";
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING:
                strCell = cell.getStringCellValue();
                break;
            case FORMULA:
                strCell = cell.getCellFormula();
                break;
            case BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                strCell = "";
                break;
            case ERROR:
                strCell = String.valueOf(cell.getErrorCellValue());
                break;
            default:
                strCell = "";
                LoggerFactory.getLogger(ExcelUtils.class).error("获取数据失败");
                break;
        }
        if (strCell == null || strCell.equals("")) {
            return "";
        }
        return strCell;
    }

    /**
     * 获取单元格数据内容为日期类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private static String getDateCellValue(Cell cell) {
        String result = "";
        try {
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTime(cell.getDateCellValue());
                    result = (calendar.get(Calendar.YEAR) + 1900) + "-" + (calendar.get(Calendar.MONTH) + 1)
                            + "-" + calendar.get(Calendar.DATE);
                    break;
                case STRING:
                    String date = getStringCellValue(cell);
                    result = date.replaceAll("[年月]", "-").replace("日", "").trim();
                    break;
                case FORMULA:
                case BOOLEAN:
                case BLANK:
                case ERROR:
                default:
                    result = "";
                    logger.error("日期格式不正确!");
                    break;
            }
        } catch (Exception e) {
            logger.error("日期格式不正确!");
            logger.error("ioException is {}", e.getMessage());
        }
        return result;
    }

    /**
     * 根据HSSFCell类型设置数据
     *
     * @param cell
     * @return
     */
    private static String getCellFormatValue(Cell cell) {
        return getCellFormatValue(cell, "");
    }

    /**
     * 根据HSSFCell类型设置数据
     *
     * @param cell
     * @param nullOrEmptyStr null或空串代替字符
     * @return
     */
    private static String getCellFormatValue(Cell cell, String nullOrEmptyStr) {
        String cellvalue = "";
        if (StringUtils.isBlank(nullOrEmptyStr)) {
            nullOrEmptyStr = "";
        }
        if (cell != null) {
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:
                case FORMULA:
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式

                        //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                        //cellvalue = cell.getDateCellValue().toLocaleString();

                        //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                        Date date = cell.getDateCellValue();
                        cellvalue = DateFormatUtils.format(date, "yyyy-MM-dd");
                    } // 如果是纯数字
                    break;
                case STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                case BOOLEAN:
                case BLANK:
                case ERROR:
                default:
                    cellvalue = nullOrEmptyStr;
                    logger.error("日期格式不正确!");
                    break;
            }
        } else {
            cellvalue = nullOrEmptyStr;
        }
        return cellvalue;
    }

    private static final char[] EXCEL_COLUMNS_NUM = new char[]{
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M', 'N', 'O',
            'P', 'Q', 'R', 'F', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
    };
}
